Imports nsDataProvider = KDESolutions.Providers.DataProvider
Imports System.Configuration
Imports System.Data.Common
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Collections.Generic
Imports Microsoft.VisualBasic
Imports System.Collections.Specialized
Imports System
Imports System.Diagnostics
Imports System.Web
Imports System.Globalization
Imports System.Security.Cryptography
Imports System.Text
Imports System.IO
Imports System.Drawing
Imports System.Drawing.Drawing2D
Imports System.Drawing.Imaging


Public Class DBImageProvider
    Inherits ImageProvider

    Private _DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString As String

    Public Const cTrueValue As String = "Y"
    Public Const cFalsevalue As String = "N"

    Public Property MyDBType() As String
        Get
            Return _DBType
        End Get
        Set(ByVal value As String)
            _DBType = value
        End Set
    End Property

    Public Overrides ReadOnly Property Name() As String
        Get
            Return "KDESolutions.Providers.Images.DBImageProvider"
        End Get
    End Property

    Public Overrides ReadOnly Property Description() As String
        Get
            Return "Provider for storing images"
        End Get
    End Property

    Public Overrides Sub Initialize(ByVal name As String, ByVal config As NameValueCollection)
        If (config Is Nothing) Then
            Throw New ArgumentNullException("config")
        End If

        If ((name Is Nothing) OrElse (name.Length = 0)) Then
            name = "ImageProvider"
        End If
        If String.IsNullOrEmpty(config("description")) Then
            config.Remove("description")
            config.Add("description", "KDESolutions Image provider")
        End If

        'Start of config file settings for database connection ******
        If String.IsNullOrEmpty("DataProviderGroup") Then
            Throw New ArgumentNullException("You must provide a DataProviderGroup")
        Else
            Dim DataProviderGroup As KDESolutions.Providers.DataProvider.DataProviderConfiguration = _
            CType(ConfigurationManager.GetSection(config("DataProviderGroup")), KDESolutions.Providers.DataProvider.DataProviderConfiguration)
            If String.IsNullOrEmpty(DataProviderGroup.DBType) = False Then
                _DBType = DataProviderGroup.DBType
                Me.MyDBType = DataProviderGroup.DBType
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBServer) = False Then
                _DBServer = DataProviderGroup.DBServer
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBUser) = False Then
                _DBUser = DataProviderGroup.DBUser
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBPassword) = False Then
                _DBPassword = DataProviderGroup.DBPassword
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBDatabaseName) = False Then
                _DBDatabaseName = DataProviderGroup.DBDatabaseName
            End If
            If String.IsNullOrEmpty(DataProviderGroup.ConnectString) = False Then
                _ConnectString = DataProviderGroup.ConnectString
            End If
        End If
    End Sub

    Public Overrides Function GetPhoto(ByVal photoid As String, ByVal size As Images.Enums.ImageEnumerations.PhotoSize) As Stream
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String
        Select Case size
            Case 1
                sSQL = "SELECT TOP 1 BytesThumb FROM Photos "
            Case 2
                sSQL = "SELECT TOP 1 BytesPoster FROM Photos "
            Case 3
                sSQL = "SELECT TOP 1 BytesFull FROM Photos "
            Case 4
                sSQL = "SELECT TOP 1 BytesOriginal FROM Photos "
            Case Else
                sSQL = "SELECT TOP 1 BytesPoster FROM Photos "
        End Select
        sSQL = sSQL & "WHERE PhotoId = ?"

        DAP.AddParameter("@PhotoID", photoid, DbType.String)
        DAP.AddParameter("@Size", CType(size, Integer), DbType.Int64)

        Dim result As Object = DAP.ExecuteScalar(sSQL)
        Try
            Return New MemoryStream(CType(result, Byte()))
        Catch
            Return Nothing
        End Try
    End Function

    Public Overrides Function GetPhoto(ByVal size As Images.Enums.ImageEnumerations.PhotoSize) As Stream
        Dim path As String = HttpContext.Current.Server.MapPath("~/Images/")
        If (size = Images.Enums.ImageEnumerations.PhotoSize.Small) Then
            path = (path + "placeholder-100.jpg")
        Else
            If (size = Images.Enums.ImageEnumerations.PhotoSize.Medium) Then
                path = (path + "placeholder-200.jpg")
            Else
                If (size = Images.Enums.ImageEnumerations.PhotoSize.Large) Then
                    path = (path + "placeholder-600.jpg")
                Else
                    path = (path + "placeholder-600.jpg")
                End If
            End If
        End If
        Return New FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read)
    End Function

    Public Overrides Function GetFirstPhoto(ByVal albumid As String, ByVal size As Images.Enums.ImageEnumerations.PhotoSize, ByVal PublicOnly As Boolean) As Stream
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)

        Dim sWhereClause As String
        If PublicOnly = True Then
            sWhereClause = "WHERE Albums.AlbumID = ? AND Albums.IsPublic = ?"
        Else
            sWhereClause = "WHERE Albums.AlbumID = ?"
        End If

        Dim sSQL As String
        Select Case size
            Case 1
                sSQL = "SELECT TOP 1 BytesThumb FROM Photos LEFT JOIN Albums ON Albums.AlbumID = Photos.AlbumID "
            Case 2
                sSQL = "SELECT TOP 1 BytesPoster FROM Photos LEFT JOIN Albums ON Albums.AlbumID = Photos.AlbumID "
            Case 3
                sSQL = "SELECT TOP 1 BytesFull FROM Photos LEFT JOIN Albums ON Albums.AlbumID = Photos.AlbumID "
            Case 4
                sSQL = "SELECT TOP 1 BytesOriginal FROM Photos LEFT JOIN Albums ON Albums.AlbumID = Photos.AlbumID "
            Case Else
                sSQL = "SELECT TOP 1 BytesPoster FROM Photos LEFT JOIN Albums ON Albums.AlbumID = Photos.AlbumID "
        End Select
        sSQL = sSQL & sWhereClause

        DAP.AddParameter("@AlbumId", albumid, DbType.String)
        If PublicOnly = True Then
            DAP.AddParameter("@IsPublic", CBoolToYesNo(PublicOnly), DbType.String)
        End If

        Dim result As Object = DAP.ExecuteScalar(sSQL)
        Try
            Return New MemoryStream(CType(result, Byte()))
        Catch e As ArgumentNullException
            Return Nothing
        End Try
    End Function

    Public Overrides Function GetPhotos(ByVal AlbumID As String, ByVal PublicOnly As Boolean) As Generic.List(Of DataTypes.Photo)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT PhotoId, Photos.AlbumId, Photos.Caption FROM Photos LEFT JOIN Albums " & _
        "ON Albums.AlbumID = Photos.AlbumID "
        Dim sWhereClause As String
        If PublicOnly = True Then
            sWhereClause = "WHERE Photos.AlbumID = ? " & _
            "AND Albums.IsPublic = ? AND Photos.LogicalDeleteInd = ?"
        Else
            sWhereClause = "WHERE Photos.AlbumID = ? AND Photos.LogicalDeleteInd = ?"
        End If
        sSQL = sSQL & sWhereClause

        DAP.AddParameter("@AlbumID", AlbumID, DbType.String)
        If PublicOnly = True Then
            DAP.AddParameter("@IsPublic", CBoolToYesNo(PublicOnly), DbType.String)
        End If
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)

        Dim list As New Generic.List(Of DataTypes.Photo)()
        Dim result As New Generic.List(Of DbDataRecord)
        result = DAP.ExecuteReader(sSQL)
        For Each reader As DbDataRecord In result
            Dim temp As New DataTypes.Photo(CStr(reader("PhotoID")), CStr(reader("AlbumID")), CStr(reader("Caption")))
            list.Add(temp)
        Next
        Return list
    End Function

    Public Overrides Function GetPhotos(ByVal PublicOnly As Boolean) As Generic.List(Of DataTypes.Photo)
        Return GetPhotos(GetRandomAlbumID())
    End Function

    Public Overrides Sub AddPhoto(ByVal AlbumID As String, ByVal Caption As String, ByVal BytesOriginal() As Byte, ByVal Username As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "INSERT INTO Photos(PhotoId,AlbumID,Caption,BytesOriginal," & _
        "BytesFull,BytesPoster,BytesThumb,CreatedBy,CreateDate,LastUpdatedBy,LastUpdatedDate,LogicalDeleteInd)" & _
        " VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"

        DAP.AddParameter("@PhotoId", System.Guid.NewGuid.ToString, DbType.String)
        DAP.AddParameter("@AlbumID", AlbumID, DbType.String)
        DAP.AddParameter("@Caption", Caption, DbType.String)
        DAP.AddParameter("@BytesOriginal", BytesOriginal, DbType.Binary)
        DAP.AddParameter("@BytesFull", ResizeImageFile(BytesOriginal, 600), DbType.Binary)
        DAP.AddParameter("@BytesPoster", ResizeImageFile(BytesOriginal, 198), DbType.Binary)
        DAP.AddParameter("@BytesThumb", ResizeImageFile(BytesOriginal, 100), DbType.Binary)
        DAP.AddParameter("@CreatedBy", Username, DbType.String)
        DAP.AddParameter("@CreateDate", Now().ToString, DbType.DateTime)
        DAP.AddParameter("@LastUpdatedBy", Username, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now().ToString, DbType.DateTime)
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)

        DAP.ExecuteNonQuery(sSQL)
    End Sub

    Public Overrides Sub RemovePhoto(ByVal PhotoID As String, ByVal Username As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "UPDATE Photos SET LastUpdatedBy = ?, LastUpdatedDate = ?, LogicalDeleteInd = ? WHERE PhotoId = ?"
        DAP.AddParameter("@LastUpdatedBy", Username, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now().ToString, DbType.DateTime)
        DAP.AddParameter("@LogicalDeleteInd", cTrueValue, DbType.String)
        DAP.AddParameter("@PhotoId", PhotoID, DbType.String)
        DAP.ExecuteNonQuery(sSQL)
    End Sub

    Public Overrides Sub EditPhoto(ByVal Caption As String, ByVal PhotoID As String, ByVal Username As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)

        Dim sSQL As String = "UPDATE (Photos) " & _
        "SET Caption = ?, LastUpdatedBy = ?, LastUpdatedDate = ? WHERE PhotoID	= ?"

        DAP.AddParameter("@Caption", Caption, DbType.String)
        DAP.AddParameter("@LastUpdatedBy", Username, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now(), DbType.DateTime)
        DAP.AddParameter("@PhotoID", PhotoID, DbType.String)

        DAP.ExecuteNonQuery(sSQL)
    End Sub

    Public Overrides Function GetAlbums(ByVal PublicOnly As Boolean) As Generic.List(Of DataTypes.Album)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT Albums.AlbumID, " & _
        "Albums.Caption, Albums.LastUpdatedBy, Albums.IsPublic, " & _
        "(SELECT Count(P.PhotoId) As NumberOfPhotos FROM Photos P WHERE P.AlbumId = Albums.AlbumID AND P.LogicalDeleteInd = ?) As NumberOfPhotos " & _
        "FROM Albums LEFT JOIN Photos " & _
        "ON Albums.AlbumID = Photos.AlbumID "
        If PublicOnly Then
            sSQL = sSQL & "WHERE (Albums.IsPublic = ? OR Albums.IsPublic = ?) AND Albums.LogicalDeleteInd = ? "
        Else
            sSQL = sSQL & "WHERE Albums.LogicalDeleteInd = ? "
        End If
        sSQL = sSQL & "Group BY Albums.AlbumID, Albums.Caption, Albums.IsPublic, Albums.LastUpdatedBy"
        DAP.AddParameter("@PLogicalDeleteInd", cFalsevalue, DbType.String)
        If PublicOnly = True Then
            DAP.AddParameter("@IsPublic", cTrueValue, DbType.String)
        End If
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)

        Dim list As New Generic.List(Of DataTypes.Album)()
        Dim result As New Generic.List(Of Common.DbDataRecord)
        result = DAP.ExecuteReader(sSQL)
        For Each reader As Common.DbDataRecord In result
            Dim temp As New DataTypes.Album(CStr(reader("AlbumID")), CInt(reader("NumberOfPhotos")), CStr(reader("Caption")), CYesNoToBool(reader("IsPublic")), CStr(reader("LastUpdatedBy")))
            list.Add(temp)
        Next
        Return list
    End Function

    Public Overrides Sub AddAlbum(ByVal Caption As String, ByVal IsPublic As Boolean, ByVal Username As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "INSERT INTO Albums (AlbumId,Caption,IsPublic,CreatedBy,CreateDate,LastUpdatedBy,LastUpdatedDate,LogicalDeleteInd) " & _
        "VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
        DAP.AddParameter("@AlbumId", System.Guid.NewGuid.ToString, DbType.String)
        DAP.AddParameter("@Caption", Caption, DbType.String)
        DAP.AddParameter("@IsPublic", CBoolToYesNo(IsPublic), DbType.String)
        DAP.AddParameter("@CreatedBy", Username, DbType.String)
        DAP.AddParameter("@CreateDate", Now(), DbType.DateTime)
        DAP.AddParameter("@LastUpdatedBy", Username, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now(), DbType.DateTime)
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        DAP.ExecuteNonQuery(sSQL)
    End Sub

    Public Overrides Sub RemoveAlbum(ByVal AlbumID As String, ByVal Username As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String
        sSQL = "SELECT PhotoId FROM Photos WHERE AlbumId = ?"
        DAP.AddParameter("@AlbumId", AlbumID, DbType.String)
        Dim result As New Generic.List(Of Common.DbDataRecord)
        result = DAP.ExecuteReader(sSQL)

        For Each resultSet As DbDataRecord In result
            sSQL = "UPDATE Photos SET LogicalDeleteInd = ?, LastUpdatedBy = ?, LastUpdatedDate = ? WHERE PhotoId = ?"
            DAP.AddParameter("@LogicalDeleteInd", cTrueValue, DbType.String)
            DAP.AddParameter("@LastUpdatedBy", Username, DbType.String)
            DAP.AddParameter("@LastUpdatedDate", Now().ToString, DbType.DateTime)
            DAP.AddParameter("@PhotoId", resultSet.Item("PhotoId"), DbType.String)
            DAP.ExecuteNonQuery(sSQL)
        Next
        sSQL = "UPDATE Albums SET LogicalDeleteInd = ?, LastUpdatedBy = ?, LastUpdatedDate = ? WHERE AlbumID = ?"
        DAP.AddParameter("@LogicalDeleteInd", cTrueValue, DbType.String)
        DAP.AddParameter("@LastUpdatedBy", Username, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now().ToString, DbType.DateTime)
        DAP.AddParameter("@AlbumId", AlbumID, DbType.String)
        DAP.ExecuteNonQuery(sSQL)
    End Sub

    Public Overrides Sub EditAlbum(ByVal Caption As String, ByVal IsPublic As Boolean, ByVal AlbumID As String, ByVal Username As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "UPDATE (Albums) " & _
         "SET Caption = ?, IsPublic = ?, LastUpdatedBy = ?, LastUpdatedDate = ? WHERE AlbumID = ?"
        DAP.AddParameter("@Caption", Caption, DbType.String)
        DAP.AddParameter("@IsPublic", CBoolToYesNo(IsPublic), DbType.String)
        DAP.AddParameter("@LastUpdatedBy", Username, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now().ToString, DbType.DateTime)
        DAP.AddParameter("@AlbumID", AlbumID, DbType.String)
        DAP.ExecuteNonQuery(sSQL)
    End Sub

    Private Function GetRandomAlbumID() As String
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT Albums.AlbumID FROM Albums LEFT JOIN Photos " & _
        "ON Albums.AlbumID = Photos.AlbumID " & _
        "WHERE Albums.IsPublic = ? AND Albums.LogicalDeleteInd = ? AND Photos.LogicalDeleteInd = ? Group BY Albums.AlbumID, " & _
        "Albums.Caption, " & _
        "Albums.IsPublic HAVING	Count(Photos.PhotoID) > 0"

        DAP.AddParameter("@IsPublic", cTrueValue, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        DAP.AddParameter("@PLogicalDeleteInd", cFalsevalue, DbType.String)
        Dim list As New Generic.List(Of DataTypes.Album)()
        Dim result As New Generic.List(Of Common.DbDataRecord)
        result = DAP.ExecuteReader(sSQL)
        For Each reader As DbDataRecord In result
            Dim temp As New DataTypes.Album(CType(reader("AlbumID"), String), 0, "", False)
            list.Add(temp)
        Next
        Try
            Dim r As Random = New Random
            Return list(r.Next(list.Count)).AlbumID
        Catch e As ArgumentOutOfRangeException
            Return -1
        End Try
    End Function

    Public Overrides Function GetRandomPhoto() As Generic.List(Of DataTypes.Photo)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT PhotoId, Photos.AlbumId, Photos.Caption FROM Photos LEFT JOIN Albums " & _
        "ON Albums.AlbumID = Photos.AlbumID " & _
        "WHERE Photos.AlbumID = ? " & _
        "AND Albums.IsPublic = ? AND Photos.LogicalDeleteInd = ?"

        DAP.AddParameter("@AlbumID", GetRandomAlbumID, DbType.String)
        DAP.AddParameter("@IsPublic", cTrueValue, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)

        Dim list As New Generic.List(Of DataTypes.Photo)()
        Dim result As New Generic.List(Of Common.DbDataRecord)
        result = DAP.ExecuteReader(sSQL)
        For Each reader As Common.DbDataRecord In result
            Dim temp As New DataTypes.Photo(CStr(reader("PhotoID")), CStr(reader("AlbumID")), CStr(reader("Caption")))
            list.Add(temp)
        Next
        Return list
    End Function

    Private Shared Function ResizeImageFile(ByVal imageFile() As Byte, ByVal targetSize As Integer) As Byte()
        Using oldImage As Image = System.Drawing.Image.FromStream(New MemoryStream(imageFile))
            Dim newSize As Size = CalculateDimensions(oldImage.Size, targetSize)
            Using newImage As Bitmap = New Bitmap(newSize.Width, newSize.Height, PixelFormat.Format24bppRgb)
                Using canvas As Graphics = Graphics.FromImage(newImage)
                    canvas.SmoothingMode = SmoothingMode.AntiAlias
                    canvas.InterpolationMode = InterpolationMode.HighQualityBicubic
                    canvas.PixelOffsetMode = PixelOffsetMode.HighQuality
                    canvas.DrawImage(oldImage, New Rectangle(New Point(0, 0), newSize))
                    Dim m As New MemoryStream
                    newImage.Save(m, ImageFormat.Jpeg)
                    Return m.GetBuffer
                End Using
            End Using
        End Using
    End Function

    Private Shared Function CalculateDimensions(ByVal oldSize As Size, ByVal targetSize As Integer) As Size
        Dim newSize As Size
        If (oldSize.Height > oldSize.Width) Then
            newSize.Width = CType((oldSize.Width * CType((targetSize / CType(oldSize.Height, Single)), Single)), Integer)
            newSize.Height = targetSize
        Else
            newSize.Width = targetSize
            newSize.Height = CType((oldSize.Height * CType((targetSize / CType(oldSize.Width, Single)), Single)), Integer)
        End If
        Return newSize
    End Function

    Public Shared Function ListUploadDirectory() As ICollection
        Dim d As DirectoryInfo = New DirectoryInfo(System.Web.HttpContext.Current.Server.MapPath("~/Upload"))
        Return d.GetFileSystemInfos("*.jpg")
    End Function

    Private Function CBoolToYesNo(ByVal InputValue As Boolean) As String
        If InputValue = True Then
            Return "Y"
        Else
            Return "N"
        End If
    End Function

    Private Function CYesNoToBool(ByVal InputString As String) As Boolean
        If InputString.ToLower = "y" Then
            Return True
        Else
            Return False
        End If
    End Function
End Class
